package br.edu.universo.si.projeto2.infojob.server.persistencia;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import br.edu.universo.si.projeto2.infojob.client.bean.BeanEstado;
import br.edu.universo.si.projeto2.infojob.client.bean.BeanEstadoCivil;
import br.edu.universo.si.projeto2.infojob.client.bean.BeanProfissionalAssinante;
import br.edu.universo.si.projeto2.infojob.server.Database;
import br.edu.universo.si.projeto2.infojob.server.excecoes.CPFExistenteException;
import br.edu.universo.si.projeto2.infojob.server.excecoes.ContaExistenteException;
import br.edu.universo.si.projeto2.infojob.server.excecoes.EMailExistenteException;

/**
 * Classe de persistência de profissionais.
 * @author Andrey A. de Oliveira
 */
public class PProfissional
{

	/**
	 * Cadastra profissional.
	 * @author Andrey A. de Oliveira
	 * @param bProfissionalAssinante : Bean de Profissional todo preenchido.
	 * @throws ClassNotFoundException
	 * @throws ContaExistenteException : exceção para contas existentes.
	 * @throws CPFExistenteException : exceção para cpf existente.
	 * @throws EMailExistenteException : exceção para e-mail já cadastrado.
	 * @throws Exception
	 */
	public static void cadastrarProfissionalAssinante(BeanProfissionalAssinante bProfissionalAssinante)
		throws ClassNotFoundException, ContaExistenteException, CPFExistenteException, EMailExistenteException, Exception
	{
		Database database = Database.getInstance();

		PreparedStatement pStmt = database.getPreparedStatement("INSERT INTO infojob.tb_profissional_assinante(cpf, rg, nome, telefone_residencial, celular, email, " +
				"data_nascimento, id_estado_civil, endereco, numero, bairro, complemento, cidade, id_estado, cep, conta, senha) " +
				"VALUES(?, ?, ?, ?, ?, ?, to_date(?, 'dd/mm/YYYY'), ?, ?, ?, ?, ?, ?, ?, ?, ?, md5(?)); ");

		pStmt.setString(1, bProfissionalAssinante.getCpf());
		pStmt.setString(2, bProfissionalAssinante.getRg());
		pStmt.setString(3, bProfissionalAssinante.getNome());
		pStmt.setString(4, bProfissionalAssinante.getTelefoneResidencial());
		pStmt.setString(5, bProfissionalAssinante.getCelular());
		pStmt.setString(6, bProfissionalAssinante.getEmail());
		pStmt.setString(7, bProfissionalAssinante.getDataNascimento());

		if (bProfissionalAssinante.getBEstadoCivil().getId() == -1)
		{
			pStmt.setNull(8, java.sql.Types.NULL);
		}
		else
		{
			pStmt.setInt(8, bProfissionalAssinante.getBEstadoCivil().getId());
		}

		pStmt.setString(9, bProfissionalAssinante.getEndereco());
		pStmt.setInt(10, bProfissionalAssinante.getNumero());
		pStmt.setString(11, bProfissionalAssinante.getBairro());
		pStmt.setString(12, bProfissionalAssinante.getComplemento());
		pStmt.setString(13, bProfissionalAssinante.getCidade());

		if (bProfissionalAssinante.getBEstado().getId() == -1)
		{
			pStmt.setNull(14, java.sql.Types.NULL);
		}
		else
		{
			pStmt.setInt(14, bProfissionalAssinante.getBEstado().getId());
		}

		pStmt.setString(15, bProfissionalAssinante.getCep());
		pStmt.setString(16, bProfissionalAssinante.getConta());
		pStmt.setString(17, bProfissionalAssinante.getSenha());

		pStmt.executeUpdate();
	}

	/**
	 * Altera os dados de um profissional.
	 * @author Andrey A. de Oliveira
	 * @param bprofissional
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
    public static void alteraDadosProfissional(BeanProfissionalAssinante bprofissional) 
            throws ClassNotFoundException, SQLException
    {
    	Database database = Database.getInstance();

    	PreparedStatement pStmt = database.getPreparedStatement("UPDATE infojob.tb_profissional_assinante SET " +
    			"cpf = ?, rg = ?, nome = ?, telefone_residencial = ?, celular = ?, data_nascimento = to_date(?, 'dd/mm/YYYY'), id_estado_civil = ?, endereco = ?, " +
    			"numero = ?, bairro = ?, complemento = ?, cidade = ?, id_estado = ?, cep = ?, email = ? " +
    			"WHERE id = ?; ");

    	pStmt.setString(1, bprofissional.getCpf());
    	pStmt.setString(2, bprofissional.getRg());
    	pStmt.setString(3, bprofissional.getNome());
    	pStmt.setString(4, bprofissional.getTelefoneResidencial());
    	pStmt.setString(5, bprofissional.getCelular());
    	pStmt.setString(6, bprofissional.getDataNascimento());

    	if (bprofissional.getBEstadoCivil().getId() == -1)
    	{
    		pStmt.setNull(7, java.sql.Types.NULL);
    	}
    	else
    	{
    		pStmt.setInt(7, bprofissional.getBEstadoCivil().getId());
    	}

    	pStmt.setString(8, bprofissional.getEndereco());
    	pStmt.setInt(9, bprofissional.getNumero());
    	pStmt.setString(10, bprofissional.getBairro());
    	pStmt.setString(11, bprofissional.getComplemento());
    	pStmt.setString(12, bprofissional.getCidade());

    	if (bprofissional.getBEstado().getId() == -1)
    	{
    		pStmt.setNull(13, java.sql.Types.NULL);
    	}
    	else
    	{
    		pStmt.setInt(13, bprofissional.getBEstado().getId());
    	}

    	pStmt.setString(14, bprofissional.getCep());
    	pStmt.setString(15, bprofissional.getEmail());
    	pStmt.setInt(16, bprofissional.getId());

    	pStmt.executeUpdate();
    }

    /**
     * Busca um profissional pelo ID.
     * @author Andrey A. de Oliveira
     * @param idProfissional
     * @return BeanProfissionalAssinante
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static BeanProfissionalAssinante getProfissionalById(Integer idProfissional) throws ClassNotFoundException, SQLException
    {
    	Database database = Database.getInstance();

    	PreparedStatement pStmt = database.getPreparedStatement(
    			"SELECT *, to_char(data_nascimento, 'dd/mm/YYYY') AS data_nascimento_str " +
    			"FROM infojob.tb_profissional_assinante WHERE id = ?; ");

    	pStmt.setInt(1, idProfissional);
    	ResultSet rs = pStmt.executeQuery();

    	if (rs.next())
    	{
    		BeanProfissionalAssinante bProfissionalAssinante = new BeanProfissionalAssinante();

    		bProfissionalAssinante.setId(rs.getInt("id"));
    		bProfissionalAssinante.setCpf(rs.getString("cpf"));
    		bProfissionalAssinante.setRg(rs.getString("rg"));
    		bProfissionalAssinante.setNome(rs.getString("nome"));
    		bProfissionalAssinante.setTelefoneResidencial(rs.getString("telefone_residencial"));
    		bProfissionalAssinante.setCelular(rs.getString("celular"));
    		bProfissionalAssinante.setDataNascimento(rs.getString("data_nascimento_str"));

    		BeanEstadoCivil bEstadoCivil = PEstadoCivil.getEstadoCivilById(rs.getInt("id_estado_civil"));
    		bProfissionalAssinante.setBEstadoCivil(bEstadoCivil);

    		bProfissionalAssinante.setEndereco(rs.getString("endereco"));
    		bProfissionalAssinante.setNumero(rs.getInt("numero"));
    		bProfissionalAssinante.setBairro(rs.getString("bairro"));
    		bProfissionalAssinante.setComplemento(rs.getString("complemento"));
    		bProfissionalAssinante.setCidade(rs.getString("cidade"));

    		BeanEstado bEstado = PEstado.getEstadoById(rs.getInt("id_estado"));
    		bProfissionalAssinante.setBEstado(bEstado);

    		bProfissionalAssinante.setCep(rs.getString("cep"));
    		bProfissionalAssinante.setConta(rs.getString("conta"));
    		bProfissionalAssinante.setEmail(rs.getString("email"));

    		return bProfissionalAssinante;
    	}
    	
    	return null;

    }

    /**
     * Verifica se o CPF do Profissional está cadastrado ou não.
     * @author Andrey A. de Oliveira
     * @param CPF
     * @return boolean
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static boolean isCPFProfissionalCadastrado(String CPF) throws ClassNotFoundException, SQLException
    {
    	Database database = Database.getInstance();
    	
    	PreparedStatement pStmt = database.getPreparedStatement("SELECT * FROM infojob.tb_profissional_assinante WHERE cpf = ?; ");
    	pStmt.setString(1, CPF);
    	
    	ResultSet rs = pStmt.executeQuery();
    	
    	if (rs.next())
    	{
    		return true;
    	}

    	return false;
    }

    /**
     * Verifica se o E-Mail do profissional está cadastrado.
     * @author Andrey A. de Oliveira
     * @param email
     * @return boolean
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static boolean isEMailProfissionalCadastrado(String email) throws ClassNotFoundException, SQLException
    {
    	Database database = Database.getInstance();
    	
    	PreparedStatement pStmt = database.getPreparedStatement("SELECT lower(email) AS email FROM infojob.tb_profissional_assinante WHERE email = lower(?); ");
    	pStmt.setString(1, email);

    	ResultSet rs = pStmt.executeQuery();
    	
    	if (rs.next())
    	{
    		return true;
    	}

    	return false;    	
    }

    /**
     * Verifica se a ID é de um profissional.
     * @author Andrey A. de Oliveira
     * @param idAssinante
     * @return boolean
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static boolean isProfissional(Integer idAssinante) throws ClassNotFoundException, SQLException
    {
    	Database database = Database.getInstance();

    	PreparedStatement pStmt = database.getPreparedStatement("SELECT * FROM infojob.tb_profissional_assinante WHERE id = ?; ");

    	pStmt.setInt(1, idAssinante);

    	ResultSet rs = pStmt.executeQuery();

    	if (rs.next())
    	{
    		return true;
    	}
    	return false;
    }

    /**
     * Exclui profissional com controle transacional.
     * @author Andrey A. de Oliveira
     * @param conn
     * @param bProfissionalAssinante
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static void excluirProfissional(Connection conn, BeanProfissionalAssinante bProfissionalAssinante)
    	throws ClassNotFoundException, SQLException
    {
    	Database database = Database.getInstance();

    	PreparedStatement pstmt = 
    		database.getPreparedStatement(conn, "DELETE FROM infojob.tb_profissional_assinante WHERE id = ?; ");

    	pstmt.setInt(1, bProfissionalAssinante.getId());
    	pstmt.executeUpdate();
    }

    /**
     * Busca Profissionais.
     * @author Marcelo M. Fleury
     * @param nome
     * @param idadeLimite
     * @param estado
     * @param curso
     * @param conhecimentos
     * @return ArrayList<BeanProfissionalAssinante> : lista de profissionais.
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static ArrayList<BeanProfissionalAssinante> buscarProfissionais(String nome, Integer idadeLimite, Integer estado, String curso, ArrayList<String> conhecimentos)
    	throws ClassNotFoundException, SQLException
    {
		ArrayList<BeanProfissionalAssinante> clProfissionais = new ArrayList<BeanProfissionalAssinante>();

		Database database = Database.getInstance();

		String query = "SELECT distinct(tb_profissional_assinante.*), to_char(data_nascimento, 'dd/mm/YYYY') AS data_nascimento_str FROM infojob.tb_profissional_assinante " + 
							"LEFT JOIN infojob.tb_profissional_curriculo ON " +
								"tb_profissional_assinante.id=tb_profissional_curriculo.id_profissional " +
							"LEFT JOIN infojob.tb_curriculo_cursos ON " +
								"tb_profissional_curriculo.id=tb_curriculo_cursos.id_curriculo " +
							"LEFT JOIN infojob.tb_curriculo_conhecimentos ON " +
								"tb_profissional_curriculo.id=tb_curriculo_conhecimentos.id_curriculo " +
							"LEFT JOIN infojob.tb_conhecimentos ON " +
								"tb_curriculo_conhecimentos.id_conhecimento=tb_conhecimentos.id " +
							"WHERE tb_profissional_assinante.conta IS NOT NULL ";

		if (nome != null && !nome.trim().equals(""))
			query += "AND replace(lower(tb_profissional_assinante.nome), ' ', '') like('%" + nome.trim() + "%') ";

		if (idadeLimite != null && idadeLimite >= 0 && idadeLimite <= 200)
			query += "AND tb_profissional_assinante.data_nascimento <= date(date(now()) - interval '" + idadeLimite + " year') ";

		if (estado != null)
			query += "AND tb_profissional_assinante.id_estado = " + estado + " ";

		if (curso != null && !curso.trim().equals(""))
			query += "AND replace(lower(tb_profissional_assinante.nome), ' ', '') like('%" + nome.trim() + "%') ";

		if (conhecimentos != null && !conhecimentos.isEmpty())
			for(int i = 0; i < conhecimentos.size(); i++)
				query += "AND replace(lower(tb_curriculo_conhecimentos.conhecimento), ' ', '') like('%" + conhecimentos.get(i) + "%') ";
		
		Statement stmt = database.getStatement();

		ResultSet rs = stmt.executeQuery(query);

		while (rs.next())
		{
			BeanProfissionalAssinante bProfissionalAssinante = new BeanProfissionalAssinante();

    		bProfissionalAssinante.setId(rs.getInt("id"));
    		bProfissionalAssinante.setCpf(rs.getString("cpf"));
    		bProfissionalAssinante.setRg(rs.getString("rg"));
    		bProfissionalAssinante.setNome(rs.getString("nome"));
    		bProfissionalAssinante.setTelefoneResidencial(rs.getString("telefone_residencial"));
    		bProfissionalAssinante.setCelular(rs.getString("celular"));
    		bProfissionalAssinante.setDataNascimento(rs.getString("data_nascimento_str"));

    		BeanEstadoCivil bEstadoCivil = PEstadoCivil.getEstadoCivilById(rs.getInt("id_estado_civil"));
    		bProfissionalAssinante.setBEstadoCivil(bEstadoCivil);

    		bProfissionalAssinante.setEndereco(rs.getString("endereco"));
    		bProfissionalAssinante.setNumero(rs.getInt("numero"));
    		bProfissionalAssinante.setBairro(rs.getString("bairro"));
    		bProfissionalAssinante.setComplemento(rs.getString("complemento"));
    		bProfissionalAssinante.setCidade(rs.getString("cidade"));

    		BeanEstado bEstado = PEstado.getEstadoById(rs.getInt("id_estado"));
    		bProfissionalAssinante.setBEstado(bEstado);

    		bProfissionalAssinante.setCep(rs.getString("cep"));
    		bProfissionalAssinante.setConta(rs.getString("conta"));
    		bProfissionalAssinante.setEmail(rs.getString("email"));

			clProfissionais.add(bProfissionalAssinante);
		}

		return clProfissionais;
    }
}